package com.pf.export;

import com.google.common.collect.Lists;
import com.pf.utils.ExportExcelUtils;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.io.FileOutputStream;
import java.util.List;

/**
 * 合并单元格
 * <p>
 * 一般数据行，我们只会合并某列（或某些列）对应的哪些行的数据，不会合并某行的哪些列数据（因为每列代表一个字段信息，合并不同字段值没什么意义，也没必要）
 * 一般表头行才会有合并哪些列的需求，表头的合并不需要用公共方法，直接用指定合并哪些行和列就行，因为需要合并哪些我们是已知的。
 * <p>
 * 参考博客：https://blog.51cto.com/u_15891242/5885893
 *
 * @author PanFei
 * @version 1.0.0
 * @createTime 2023/3/5 18:42
 */
@Slf4j
public class MergedCellDemo {

    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    public static class StudentScore {
        private String classInfo;
        private String stuName;
        private float score;
        private Integer rank;
    }

    public static void main(String[] args) throws Exception {
        mergedCellTest();
        mergedCellTest2();
    }

    /**
     * 合并单元格
     */
    private static void mergedCellTest() throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("合并单元格");
        sheet.setDefaultColumnWidth(20);

        // 表头信息
        int rowIndex = 0;
        XSSFCellStyle headCellStyle = ExportExcelUtils.getHeadCellStyle(workbook);
        XSSFRow headRow = sheet.createRow(rowIndex);
        XSSFCell headRowCell = headRow.createCell(0);
        headRowCell.setCellStyle(headCellStyle);
        headRowCell.setCellValue("软件一班成绩表");
        rowIndex = rowIndex + 1;
        List<String> headTitles = Lists.newArrayList("班级", "姓名", "成绩", "排名");
        XSSFRow headRow2 = sheet.createRow(rowIndex);
        for (int i = 0; i < headTitles.size(); i++) {
            XSSFCell headCell2 = headRow2.createCell(i);
            headCell2.setCellValue(headTitles.get(i));
            headCell2.setCellStyle(headCellStyle);
        }

        // 数据行
        XSSFCellStyle dataCellStyle = ExportExcelUtils.getDataCellStyle(workbook);
        List<StudentScore> studentScoreList = getStudentScoreList();
        for (int i = 0; i < studentScoreList.size(); i++) {
            rowIndex = rowIndex + 1;
            StudentScore studentScore = studentScoreList.get(i);
            XSSFRow dataRow = sheet.createRow(rowIndex);
            XSSFCell cell = dataRow.createCell(0);
            cell.setCellValue(studentScore.getClassInfo());
            cell.setCellStyle(dataCellStyle);

            XSSFCell cell2 = dataRow.createCell(1);
            cell2.setCellValue(studentScore.getStuName());
            cell2.setCellStyle(dataCellStyle);

            XSSFCell cell3 = dataRow.createCell(2);
            cell3.setCellValue(studentScore.getScore());
            cell3.setCellStyle(dataCellStyle);

            XSSFCell cell4 = dataRow.createCell(3);
            cell4.setCellValue(studentScore.getRank());
            cell4.setCellStyle(dataCellStyle);
        }

        // 单元格合并
        // 将第一行，第一列至第四列合并（起始行，终止行，起始列，终止列）
        CellRangeAddress cellRange = new CellRangeAddress(0, 0, 0, 3);
        sheet.addMergedRegion(cellRange);

        // 将第3行至第6行的第1列合并
        CellRangeAddress cellRange2 = new CellRangeAddress(2, 5, 0, 0);
        sheet.addMergedRegion(cellRange2);

        // 将第3行至第4行的第3列合并
        CellRangeAddress cellRange3 = new CellRangeAddress(2, 3, 2, 2);
        sheet.addMergedRegion(cellRange3);

        // 将第3行至第4行的第4列合并
        CellRangeAddress cellRange4 = new CellRangeAddress(2, 3, 3, 3);
        sheet.addMergedRegion(cellRange4);

        workbook.write(new FileOutputStream("D:/test/mergedCell.xlsx"));
        System.out.println(">>>> 导出成功...");
    }

    /**
     * 利用公共方法合并数据行某些列中行内容相同的数据，其实就是行合并
     *
     * @throws Exception
     */
    private static void mergedCellTest2() throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("合并单元格");
        sheet.setDefaultColumnWidth(20);

        // 表头信息
        int rowIndex = 0;
        XSSFCellStyle headCellStyle = ExportExcelUtils.getHeadCellStyle(workbook);
        XSSFRow headRow = sheet.createRow(rowIndex);
        XSSFCell headRowCell = headRow.createCell(0);
        headRowCell.setCellStyle(headCellStyle);
        headRowCell.setCellValue("软件一班成绩表");
        rowIndex = rowIndex + 1;
        List<String> headTitles = Lists.newArrayList("班级", "姓名", "成绩", "排名");
        XSSFRow headRow2 = sheet.createRow(rowIndex);
        for (int i = 0; i < headTitles.size(); i++) {
            XSSFCell headCell2 = headRow2.createCell(i);
            headCell2.setCellValue(headTitles.get(i));
            headCell2.setCellStyle(headCellStyle);
        }

        // 数据行
        XSSFCellStyle dataCellStyle = ExportExcelUtils.getDataCellStyle(workbook);
        List<StudentScore> studentScoreList = getStudentScoreList();
        for (int i = 0; i < studentScoreList.size(); i++) {
            rowIndex = rowIndex + 1;
            StudentScore studentScore = studentScoreList.get(i);
            XSSFRow dataRow = sheet.createRow(rowIndex);
            XSSFCell cell = dataRow.createCell(0);
            cell.setCellValue(studentScore.getClassInfo());
            cell.setCellStyle(dataCellStyle);

            XSSFCell cell2 = dataRow.createCell(1);
            cell2.setCellValue(studentScore.getStuName());
            cell2.setCellStyle(dataCellStyle);

            XSSFCell cell3 = dataRow.createCell(2);
            cell3.setCellValue(studentScore.getScore());
            cell3.setCellStyle(dataCellStyle);

            XSSFCell cell4 = dataRow.createCell(3);
            cell4.setCellValue(studentScore.getRank());
            cell4.setCellStyle(dataCellStyle);
        }

        // 合并表头列
        CellRangeAddress cellRange = new CellRangeAddress(0, 0, 0, 3);
        sheet.addMergedRegion(cellRange);

        // 利用公共方法合并某些列对应需合并的行数据（因为表头占了两行，所以数据行下标从2开始）
        // 需要合并的列是第0,2,3列，分别是 班级。 成绩，排名列
        ExportExcelUtils.mergeRowCells(sheet, studentScoreList, 2, 0, 2, 3);

        workbook.write(new FileOutputStream("D:/test/mergedCell2.xlsx"));
        System.out.println(">>>> 导出成功...");
    }


    private static List<StudentScore> getStudentScoreList() {
        StudentScore s1 = new StudentScore("软件一班", "张三", 95, 1);
        StudentScore s2 = new StudentScore("软件一班", "李四", 95, 1);
        StudentScore s3 = new StudentScore("软件一班", "王五", 80, 3);
        StudentScore s4 = new StudentScore("软件一班", "马六", 70, 4);
        return Lists.newArrayList(s1, s2, s3, s4);
    }
}
